﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

namespace Discio
{
    public enum SqlAction
    {
        DataOnly = 1,
        DataAndStructure = 2,
        StructureOnly = 3
    }

    public enum TypeFormat
    {
        Quote = 0,
        NoQuote = 1
    }

    public static class ToSqlServerExtension
    {

        public static string ToSQL(this  IDiscio d, SqlAction action = SqlAction.DataOnly)
        {
            StringBuilder o = new StringBuilder();

            if (((int)action) > 1)
            {
                o.Append(TableStructure(d));
                o.Append(Environment.NewLine);
            }

            if (((int)action) < 3)
            {
                o.Append(Data(d));
                o.Append(Environment.NewLine);
            }

            return o.ToString();

        }

        private static string Data(IDiscio d)
        {
            StringBuilder o = new StringBuilder();
            string table = d.GetType().Name;
               PropertyInfo[] ps = d.GetType().GetProperties();
               List<string> columns = new List<string>();
               List<string> values = new List<string>();
               List<TypeFormat> typeFormat = new List<TypeFormat>();

               foreach (PropertyInfo p in ps)
               { 
                   columns.Add(p.Name);
                   values.Add(p.GetValue(d, null).ToString().Replace("'","''"));
                   typeFormat.Add(SelectFormat(DataTypeMap(p.PropertyType.Name)));
               }

               o.Append(@"INSERT INTO ["+table+"]");
               o.Append(Environment.NewLine);

               StringBuilder c = new StringBuilder();
               StringBuilder v = new StringBuilder();

               for (int i = 0; i < columns.Count; i++)
               {
                   c.Append("["+columns[i]+"],");
                   c.Append(Environment.NewLine);

                   if (typeFormat[i] == TypeFormat.NoQuote)
                   {
                       v.Append(values[i]+",");
                   }
                   else
                   {
                        v.Append("'" + values[i] + "',");
                   }
                   
                   v.Append(Environment.NewLine);

               }

               o.Append("(");
               o.Append(Environment.NewLine);

               o.Append(c.ToString().TrimEnd().TrimEnd(','));

               o.Append(")");
               o.Append(Environment.NewLine);
               o.Append(@"VALUES");
               o.Append(Environment.NewLine);
               o.Append("(");
               o.Append(Environment.NewLine);

               o.Append(v.ToString().TrimEnd().TrimEnd(','));

               o.Append(");");
               o.Append(Environment.NewLine);

                   return o.ToString();
        }

        private static string TableStructure(IDiscio d)
        {
            StringBuilder o = new StringBuilder();

            string table = d.GetType().Name;

            o.Append(Environment.NewLine);
            o.Append(@"CREATE TABLE [" + table + "](");
            o.Append(Environment.NewLine);
            o.Append(Environment.NewLine);

            StringBuilder properties = new StringBuilder();

            properties.Append("[ID] UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,".PadLeft(10));


            PropertyInfo[] ps = d.GetType().GetProperties();
            foreach (PropertyInfo p in ps)
            {
                if (p.Name != "ID")
                {
                    string type = DataTypeMap(p.PropertyType.Name);
                    properties.Append(Environment.NewLine);
                    properties.Append(("[" + p.Name + "] " + type + ",").PadLeft(10));

                }

            }
            o.Append(properties.ToString().Trim().TrimEnd(','));
            o.Append(Environment.NewLine);
            o.Append(Environment.NewLine);
            o.Append(@");");
            o.Append(Environment.NewLine);


            return o.ToString();
        }

        private static TypeFormat SelectFormat(string t)
        {
            TypeFormat r = TypeFormat.Quote;

            switch (t)
            {
                case "Int64":
                case "Nullable<Int64>":
                case "Decimal":
                case "Nullable<Decimal>":
                case "Double":
                case "Nullable<Double>": 
                case "Int":
                case "Nullable<Int32>":
                case "Int32":
                case "Int16":
                case "Byte": r = TypeFormat.NoQuote;
                    break;
                     

            }

            return r;
        }

        private static string DataTypeMap(string t)
        {
            string r = "nvarchar(max)";

            switch(t)
            {
                case "Int64":
                case "Nullable<Int64>": r ="bigint";
                    break;

                case "Byte[]": r = "binary";
                    break;

                case "Boolean":
                case "Nullable<Boolean>": r = "bit";
                    break;

                case "DateTime": 
                case "DateTime?": 
                case "Nullable<DateTime>": r = "datetime";
                    break;

                case "DateTimeOffset": r = "DATETIMEOFFSET";
                    break;

                case "Decimal":
                case "Nullable<Decimal>": r = "decimal";
                    break;

                case "Double":
                case "Nullable<Double>": r = "float";
                    break;

                case "Int": 
                case "Nullable<Int32>":
                case "Int32": r = "int";
                    break;

                case "Int16": r = "smallint";
                    break;

                case "String":
                case "Char[]": r = "nvarchar(max)";
                    break;
                case "Char": r = "nchar";
                    break;
                case "Single": r = "real";
                    break;

                case "TimeSpan": r = "time";
                    break;

                case "Byte": r = "tinyint";
                    break;

                case "Xml": r = "xml";
                    break;

            }

            return r;
        }

    }
}
